﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Transactions;
using AKMII.DMRA.Common;

namespace AKMII.DMRA.DataAccess
{
    public class OrderAccessor
    {
        /// <summary>
        /// Add a new order for MD at first time.
        /// </summary>
        /// <param name="userId">MD's id.</param>
        /// <param name="year">Current year number.</param>
        /// <param name="month">Current month number.</param>
        /// <param name="zipIds">Zip contained in Order.</param>
        /// <returns></returns>
        public bool AddOrder(int orderId, string userId, int year, int month, string zipIds)
        {
            const string SpName = "sp_Insert_Order";

            try
            {
                object[] parameters = new object[]
                    {
                        orderId,
                        userId,
                        year,
                        month,
                        zipIds
                    };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add order", ex);
            }

            return true;
        }

        /// <summary>
        /// Add a new order for MD at first time.
        /// </summary>
        /// <param name="userId">MD's id.</param>
        /// <param name="year">Current year number.</param>
        /// <param name="month">Current month number.</param>
        /// <param name="zipIds">Zip contained in Order.</param>
        /// <returns></returns>
        public bool AddOrderNew(int orderId, string userId, int year, int month, string zipIds, string assignment)
        {
            const string SpName = "sp_Insert_OrderNew";

            try
            {
                object[] parameters = new object[]
                    {
                        orderId,
                        userId,
                        year,
                        month,
                        zipIds,
                        assignment
                    };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add order new", ex);
            }

            return true;
        }

        public bool AddOrderRepeatRemark(string mdnumber, int month, int year, int zipId, string reason)
        {
            const string SpName = "sp_Insert_RepeatOrderRemark";

            try
            {
                object[] parameters = new object[]
                    {
                        mdnumber,
                        month,
                        year,
                        zipId,
                        reason
                    };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add OrderRepeatRemark", ex);
            }

            return true;
        }

        public bool AddMapOrder(int orderId, string userId, int year, int month, string zipIds)
        {
            const string SpName = "sp_Map_Insert_Order";

            try
            {
                object[] parameters = new object[]
                    {
                        orderId,
                        userId,
                        year,
                        month,
                        zipIds
                    };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add order for map", ex);
            }

            return true;
        }

        public bool DeleteMapOrder(int orderId, string userId, int year, int month, string zipIds)
        {
            const string SpName = "sp_Map_Delete_Order";

            try
            {
                object[] parameters = new object[]
                    {
                        orderId,
                        userId,
                        year,
                        month,
                        zipIds
                    };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in delete order for map", ex);
            }

            return true;
        }

        /// <summary>
        /// Chnange the Order's status
        /// </summary>
        /// <param name="orderId">Order Id.</param>
        /// <returns></returns>
        public bool UpdateOrderStatus(int orderId)
        {
            const string SpName = "sp_Purchase_Order";

            try
            {
                object[] parameters = new object[]
                {
                    orderId,
                    1
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update order", ex);
            }

            return true;
        }

        /// <summary>
        /// Chnange the Order's status
        /// </summary>
        /// <param name="orderId">Order Id.</param>
        /// <returns></returns>
        public bool UpdateOrderStatus(int orderId, int PurchasedStatus)
        {
            const string SpName = "sp_Purchase_Order";
            try
            {
                object[] parameters = new object[]
                {
                    orderId,
                    PurchasedStatus
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update order", ex);
            }
            return true;
        }

        public List<LeadBlock> GetOrderHistory(string userId, int year, int month)
        {
            List<LeadBlock> zips = new List<LeadBlock>();
            const string SpName = "sp_Get_OrderHistory";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[]
                {
                    userId,
                    year.ToString(),
                    month.ToString()
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    LeadBlock zip = new LeadBlock();

                    zip.ZipId = reader.GetInt32(0);
                    zip.LeadBlockCode = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    zip.ZipName = zip.LeadBlockCode.Substring(0, 5);
                    zip.County = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    zip.UserId = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    zip.State = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    zip.City = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    zip.Division = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    zip.Market = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    //zip.Territory = !reader.IsDBNull(8) ? reader.GetString(8) : null;
                    zip.ActiveLeadCount = reader.GetInt32(9);
                    zip.LapsedLeadCount = reader.GetInt32(10);
                    zip.BusinessLeadCount = reader.GetInt32(11);
                    zip.NeighborLeadCount = reader.GetInt32(12);
                    zip.IsOrderInOneALN = Convert.ToBoolean(reader.GetInt32(13));
                    zip.IsOrderInOneBus = Convert.ToBoolean(reader.GetInt32(14));
                    zip.IsOrderInTwoALN = Convert.ToBoolean(reader.GetInt32(15));
                    zip.IsOrderInTwoBus = Convert.ToBoolean(reader.GetInt32(16));
                    zip.IsOrderInThreeALN = Convert.ToBoolean(reader.GetInt32(17));
                    zip.IsOrderInThreeBus = Convert.ToBoolean(reader.GetInt32(18));
                    zip.IsOrderInFourALN = Convert.ToBoolean(reader.GetInt32(19));
                    zip.IsOrderInFourBus = Convert.ToBoolean(reader.GetInt32(20));
                    zip.IsOrderInFiveALN = Convert.ToBoolean(reader.GetInt32(21));
                    zip.IsOrderInFiveBus = Convert.ToBoolean(reader.GetInt32(22));
                    zip.IsOrderInSixALN = Convert.ToBoolean(reader.GetInt32(23));
                    zip.IsOrderInSixBus = Convert.ToBoolean(reader.GetInt32(24));
                    zip.IsOrderInSevenALN = Convert.ToBoolean(reader.GetInt32(25));
                    zip.IsOrderInSevenBus = Convert.ToBoolean(reader.GetInt32(26));
                    zip.IsOrderInEightALN = Convert.ToBoolean(reader.GetInt32(27));
                    zip.IsOrderInEightBus = Convert.ToBoolean(reader.GetInt32(28));
                    zip.IsOrderInNineALN = Convert.ToBoolean(reader.GetInt32(29));
                    zip.IsOrderInNineBus = Convert.ToBoolean(reader.GetInt32(30));
                    zip.IsOrderInTenALN = Convert.ToBoolean(reader.GetInt32(31));
                    zip.IsOrderInTenBus = Convert.ToBoolean(reader.GetInt32(32));
                    zip.IsOrderInElevenALN = Convert.ToBoolean(reader.GetInt32(33));
                    zip.IsOrderInElevenBus = Convert.ToBoolean(reader.GetInt32(34));
                    zip.IsOrderInTwelveALN = Convert.ToBoolean(reader.GetInt32(35));
                    zip.IsOrderInTwelveBus = Convert.ToBoolean(reader.GetInt32(36));

                    zips.Add(zip);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get zips by id", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return zips;
        }

        public List<OrderInfo> GetOrderInfo(int month, int year)
        {
            List<OrderInfo> orderInfos = new List<OrderInfo>();
            const string SpName = "sp_Get_OrderInfo";
            SqlDataReader reader = null;
            
            try
            {
                object[] parameters = new object[] { month, year };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    OrderInfo orderInfo = new OrderInfo();

                    orderInfo.Division = !reader.IsDBNull(0) ? reader.GetString(0) : string.Empty;
                    orderInfo.Market = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    orderInfo.AccountName = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    orderInfo.ActiveLeadCount = reader.GetInt32(3);
                    orderInfo.LapsedLeadCount = reader.GetInt32(4);
                    orderInfo.NeighborLeadCount = reader.GetInt32(5);
                    orderInfo.BusinessLeadCount = reader.GetInt32(6);
                    orderInfo.UpdateTS = reader.GetDateTime(7);
                    orderInfo.AgentNum = reader.GetInt32(8);

                    orderInfos.Add(orderInfo);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get order information by month and year", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return orderInfos;
        }

        public List<OrderInfo> GetOrderMoreInfo(int month, int year)
        {
            List<OrderInfo> orderInfos = new List<OrderInfo>();
            const string SpName = "sp_Get_OrderInfo_New";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { month, year };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    OrderInfo orderInfo = new OrderInfo();

                    orderInfo.Division = !reader.IsDBNull(0) ? reader.GetString(0) : string.Empty;
                    orderInfo.Market = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    orderInfo.AccountName = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    orderInfo.ActiveStopCount = reader.GetInt32(3);
                    orderInfo.LapsedStopCount = reader.GetInt32(4);
                    orderInfo.NeighborStopCount = reader.GetInt32(5);
                    orderInfo.BusinessStopCount = reader.GetInt32(6);
                    orderInfo.UpdateTS = reader.GetDateTime(7);
                    orderInfo.AgentNum = reader.GetInt32(8);
                    orderInfo.AgentAlphaRosterCount = reader.GetInt32(9);
                    orderInfo.AlphaRosterOrderCount = reader.GetInt32(10);
                    orderInfo.AgentCreatedCount = reader.GetInt32(11);
                    orderInfo.AgentCreatedOrderCount = reader.GetInt32(12);
                    orderInfo.TotalAssignments = reader.GetInt32(13);
                    orderInfo.ActiveLeadCount = reader.GetInt32(14);
                    orderInfo.LapsedLeadCount = reader.GetInt32(15);
                    orderInfo.NeighborLeadCount = orderInfo.NeighborStopCount;
                    orderInfo.BusinessLeadCount = orderInfo.BusinessStopCount;
                    orderInfo.MdNumber = !reader.IsDBNull(16) ? reader.GetString(16) : string.Empty;

                    orderInfos.Add(orderInfo);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get order information by month and year", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return orderInfos;
        }

        public Order GetOrderByIdAndDate(string id, int month, int year)
        {
            Order order = null;
            const string SpName = "sp_Get_Order";
            SqlDataReader reader = null;
            try
            {
                object[] parameters = new object[]
                {
                    id,
                    month,
                    year
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                if (reader.Read())
                {
                    order = new Order();
                    order.Id = reader.GetInt32(0);
                    order.AccountId = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    order.PeroidYear = Convert.ToInt32(reader.GetString(2));
                    order.PeroidMonth = Convert.ToInt32(reader.GetString(3));
                    order.IsPurchased = reader.GetBoolean(4);
                    order.IsAssigned = reader.IsDBNull(6) ? true : false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get order by id and date", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return order;
        }

        public List<Order> GetOrders(int month, int year)
        {
            List<Order> orders = new List<Order>(); ;
            const string SpName = "sp_Get_OrderSummary";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[]
                {
                    year.ToString(),
                    month.ToString()
                };

                reader = SqlHelper.ExecuteReaderNoTimeout(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Order order = new Order();

                    order.MarketingDirector = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    order.AgentNumber = reader.GetInt32(1);
                    order.TargetCount = reader.GetInt32(2);
                    order.ActualCount = reader.GetInt32(3);

                    orders.Add(order);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get order by id and date", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return orders;
        }

        public List<RepeatReason> GetRepeatOrderReason(int month, int year)
        {
            List<RepeatReason> reasons = new List<RepeatReason>();
            const string SpName = "sp_Get_RepeatReport";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { month, year };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    RepeatReason reason = new RepeatReason();

                    reason.MdNumber = !reader.IsDBNull(0) ? reader.GetString(0) : string.Empty;
                    reason.Division = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    reason.Market = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    reason.City = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    reason.LeadblockCode = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    reason.AssignmentName = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                    reason.Reason = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                    reason.ZipId = reader.GetInt32(7);

                    reasons.Add(reason);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get repeat reason", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return reasons;
        }
    }
}
